package com.example.springboot.mapper;

import com.example.springboot.entity.Applications;
import com.example.springboot.entity.interviews;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface InterviewMapper {

    @Select("SELECT\n" +
            "\tstudents.`name`, \n" +
            "\tstudents.student_id, \n" +
            "\tstudents.user_id, \n" +
            "\tstudents.gender, \n" +
            "\tstudents.phone, \n" +
            "\tapplications.application_id, \n" +
            "\tapplications.application_status, \n" +
            "\tapplications.applied_at, \n" +
            "\tapplications.updated_at, \n" +
            "\tjobs.job_id, \n" +
            "\tjobs.company_id, \n" +
            "\tjobs.job_title\n" +
            "FROM\n" +
            "\tstudents\n" +
            "\tRIGHT JOIN\n" +
            "\tapplications\n" +
            "\tON \n" +
            "\t\tstudents.student_id = applications.student_id\n" +
            "\tRIGHT JOIN\n" +
            "\tjobs\n" +
            "\tON \n" +
            "\t\tapplications.job_id = jobs.job_id\n" +
            "WHERE\n" +
            "\tstudents.student_id = applications.student_id AND\n" +
            "\tapplications.job_id = jobs.job_id AND\n" +
            "\tapplications.application_status <> \"submitted\" ")
    List<interviews> getAllInterviews();

//    @Select("SELECT i.*, a.application_id, a.job_id, s.name AS student_name, j.job_title, i.interview_date, i.interview_time, i.location " +
//            "FROM interviews i " +
//            "INNER JOIN applications a ON i.application_id = a.application_id " +
//            "INNER JOIN students s ON a.student_id = s.student_id " +
//            "INNER JOIN jobs j ON a.job_id = j.job_id")
//    List<interviews> getAllInterviews();

    @Select("SELECT i.*, a.application_id, a.job_id, s.name AS student_name, j.job_title, i.interview_date, i.interview_time, i.location " +
            "FROM interviews i " +
            "INNER JOIN applications a ON i.application_id = a.application_id " +
            "INNER JOIN students s ON a.student_id = s.student_id " +
            "INNER JOIN jobs j ON a.job_id = j.job_id " +
            "WHERE i.interview_id = #{id}")
    interviews getInterviewById(Long id);

    @Insert("INSERT INTO interviews (student_id, job_id, interview_date, interview_time, location) " +
            "VALUES (#{student_id}, #{job_id}, #{interview_date}, #{interview_time}, #{location})")
    @Options(useGeneratedKeys = true, keyProperty = "interview_id")
    void createInterview(interviews interview);

    @Update("UPDATE interviews SET " +
            "interview_date = #{interview_date}, interview_time = #{interview_time}, " +
            "location = #{location} WHERE interview_id = #{interview_id}")
    void updateInterview(interviews interview);

    @Delete("DELETE FROM interviews WHERE interview_id = #{id}")
    void deleteInterview(Long id);

    @Delete("<script>" +
            "DELETE FROM interviews WHERE interview_id IN " +
            "<foreach item='id' collection='list' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    void batchDeleteInterviews(@Param("list") List<Long> ids);

    @Select("SELECT i.*, a.application_id, a.job_id, s.name AS student_name, j.job_title, i.interview_date, i.interview_time, i.location " +
            "FROM interviews i " +
            "INNER JOIN applications a ON i.application_id = a.application_id " +
            "INNER JOIN students s ON a.student_id = s.student_id " +
            "INNER JOIN jobs j ON a.job_id = j.job_id " +
            "WHERE s.name LIKE CONCAT('%', #{keyword}, '%') OR j.job_title LIKE CONCAT('%', #{keyword}, '%')")
    List<interviews> searchInterviews(@Param("keyword") String keyword);

    @Select("select *from interviews ")
    List<interviews> getInterDataAll();

    @Update("UPDATE applications SET " +
            "application_status = #{application_status}" +
            "WHERE application_id = #{application_id}")
    void updateAPPInterview(Applications application);

//    @Select("SELECT\n" +
//            "\t*\n" +
//            "FROM\n" +
//            "\tapplications\n" +
//            "WHERE\n" +
//            "\tapplications.application_status = \"shortlisted\"")
//    List<applications> getEligibleApplicants();
}
